﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
    public static class PaySpend
    {
        public static DataTable GetBianhaoList()
        {
            return DBHelper.GetDataSet("select 编号 from PaySpend");
        }
        public static DataTable GetList()
        {
            return DBHelper.GetDataSet("select * from PaySpend");
        }
        public static int GetCount(string tablename)
        {
            string str = "select count(1) from " + tablename;
            return DBHelper.GetScalar(str);
        }
        public static int SpendCount()
        {
            return GetCount("PaySpend");
        }

        public static string GetSpendBalance(int rIndex)
        {
            double Sum=0.00;
            string sql = "select 金额 from PaySpend";
            DataTable dt = DBHelper.GetDataSet(sql);
            for (int i = 0; i < rIndex; i++ )
            {
                Sum += Convert.ToDouble(dt.Rows[i][0]);
            }
            return Sum.ToString() + ".00" ;
        }
        public static string[] GetSpendBalance()
        {
            double Sum = 0.00; 
            string stcur = DateTime.Now.ToString("yyyy-MM-dd");
            DateTime cur = Convert.ToDateTime(stcur);

//             int day = Convert.ToInt32(stcur.Substring(8, 2));
//            string stbegin = stcur.Substring(0, stcur.Length-2)+"01"; //设置统计起始时间  该月1日起
            
            //暂行方案
            string stbegin = "2012-10-26";
            int day = 7;

            DateTime begin = Convert.ToDateTime(stbegin);
            string stime = DateTime.Now.ToString("dd");
            string sql = string.Format("select 金额 from PaySpend where 收支 = '支出' and 日期 between '{0}' and '{1}'", begin, cur);
            DataTable dt = DBHelper.GetDataSet(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Sum += System.Math.Abs(Convert.ToDouble(dt.Rows[i][0]));
            }
            double avg = Sum/day;

            string[] str = new string[2];
            str[0] = Sum.ToString(); //总数
            str[1] = avg.ToString("0.00"); //平均（该个月1日到当日内的平均消费）
            return str;
        }
        public static DataTable GetSpendList()
        {
            string field = "编号, 类型, 收支, 金额, 相关人, 日期, 操作员,余额 , 备注";
            string sql = string.Format("select {0} from PaySpend", field);
            return DBHelper.GetDataSet(sql);
        }
        public static DataTable GetCardList()
        {
            string field = "卡号, 发卡行, 持卡人, 类型, 金额, 相关人, 日期, 进出, 操作员, 备注";
            string sql = string.Format("select {0} from CreteCard order by 卡号", field);
            return DBHelper.GetDataSet(sql);
        }
        public static DataTable GetSpendList(string id)
        {
            string sql = string.Format("select * from PaySpend where 序号={0}", id);
            return DBHelper.GetDataSet(sql);
        }
        public static int Del(string id)
        {
            string sql = string.Format("delete from PaySpend where 编号 in ({0})", id);
            return DBHelper.ExecuteCommand(sql);
        }

        public static int AddSpend(string fValue)
        {
            string field = "编号, 类型, 金额, 相关人, 日期, 操作员, 备注, 收支";
            string fieldvalue = fValue;
            string sql = string.Format("insert into PaySpend({0}) values({1})", field, fieldvalue);
            return DBHelper.ExecuteCommand(sql);
        }

        public static int AddDetail(string fValue)
        {
            string field = "编号, 明细, 金额, 备注";
            string fieldvalue = fValue;
            string sql = string.Format("insert into PayDetail({0}) values({1})", field, fieldvalue);
            return DBHelper.ExecuteCommand(sql);
        }
        public static int UpdateDetail(Model.PayDetail detail)
        {
            string sql = @"update PayDetail set 明细 ='" + detail.明细 + "' , 金额='" + detail.金额 + "', 备注='" + detail.备注 + "' where 编号 = '"+detail.编号+"'";
            return DBHelper.ExecuteCommand(sql);
        }
        public static int UpdateDetailByID(Model.PayDetail detail)
        {
            string sql = @"update PayDetail set 明细 ='" + detail.明细 + "' , 金额='" + detail.金额 + "', 备注='" + detail.备注 + "' where 编号 = '" + detail.编号 + "'";
            return DBHelper.ExecuteCommand(sql);
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public static int Add(Model.PaySpend model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into PaySpend(");
            strSql.Append("编号,类型,金额,相关人,日期,操作员,备注,收支)");
            strSql.Append(" values (");
            strSql.Append("@编号,@类型,@金额,@相关人,@日期,@操作员,@备注,@收支)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@编号", SqlDbType.NVarChar),
					new SqlParameter("@类型", SqlDbType.NVarChar),
					new SqlParameter("@金额", SqlDbType.Decimal,9),
					new SqlParameter("@相关人", SqlDbType.NVarChar),
					new SqlParameter("@日期", SqlDbType.DateTime),
					new SqlParameter("@操作员", SqlDbType.NVarChar),
					new SqlParameter("@备注", SqlDbType.NVarChar),
					new SqlParameter("@收支", SqlDbType.NVarChar,50)};
            parameters[0].Value = model.编号;
            parameters[1].Value = model.类型;
            parameters[2].Value = model.金额;
            parameters[3].Value = model.相关人;
            parameters[4].Value = model.日期;
            parameters[5].Value = model.操作员;
            parameters[6].Value = model.备注;
            parameters[7].Value = model.收支;

            int obj = DBHelper.ExecuteCommand(strSql.ToString(), parameters);
            if (obj == 0)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public static bool UpdateSpend(Model.PaySpend payspend)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update PaySpend set ");
            strSql.Append("编号=@编号,");
            strSql.Append("类型=@类型,");
            strSql.Append("金额=@金额,");
            strSql.Append("相关人=@相关人,");
            strSql.Append("日期=@日期,");
            strSql.Append("操作员=@操作员,");
            strSql.Append("备注=@备注,");
            strSql.Append("收支=@收支");
            strSql.Append(" where 序号=@序号");
            SqlParameter[] parameters = {
					new SqlParameter("@编号", SqlDbType.NVarChar),
					new SqlParameter("@类型", SqlDbType.NVarChar),
					new SqlParameter("@金额", SqlDbType.Decimal,9),
					new SqlParameter("@相关人", SqlDbType.NVarChar),
					new SqlParameter("@日期", SqlDbType.DateTime),
					new SqlParameter("@操作员", SqlDbType.NVarChar),
					new SqlParameter("@备注", SqlDbType.NVarChar),
					new SqlParameter("@收支", SqlDbType.NVarChar,50),
					new SqlParameter("@序号", SqlDbType.Int,4)};
            parameters[0].Value = payspend.编号;
            parameters[1].Value = payspend.类型;
            parameters[2].Value = payspend.金额;
            parameters[3].Value = payspend.相关人;
            parameters[4].Value = payspend.日期;
            parameters[5].Value = payspend.操作员;
            parameters[6].Value = payspend.备注;
            parameters[7].Value = payspend.收支;
            parameters[8].Value = payspend.序号;

            int rows = DBHelper.ExecuteCommand(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public static Model.PaySpend GetModel(int 序号)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 序号,编号,类型,金额,相关人,日期,操作员,备注,余额,收支 from PaySpend ");
            strSql.Append(" where 序号=@序号");
            SqlParameter[] parameters = {
					new SqlParameter("@序号", SqlDbType.Int,4)
			};
            parameters[0].Value = 序号;

            Model.PaySpend model = new Model.PaySpend();
            DataTable dt = DBHelper.GetDataSet(strSql.ToString(), parameters);
            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public static Model.PaySpend DataRowToModel(DataRow row)
        {
            Model.PaySpend model = new Model.PaySpend();
            if (row != null)
            {
                if (row["序号"] != null && row["序号"].ToString() != "")
                {
                    model.序号 = int.Parse(row["序号"].ToString());
                }
                if (row["编号"] != null)
                {
                    model.编号 = row["编号"].ToString();
                }
                if (row["类型"] != null)
                {
                    model.类型 = row["类型"].ToString();
                }
                if (row["金额"] != null && row["金额"].ToString() != "")
                {
                    model.金额 = decimal.Parse(row["金额"].ToString());
                }
                if (row["相关人"] != null)
                {
                    model.相关人 = row["相关人"].ToString();
                }
                if (row["日期"] != null && row["日期"].ToString() != "")
                {
                    model.日期 = DateTime.Parse(row["日期"].ToString());
                }
                if (row["操作员"] != null)
                {
                    model.操作员 = row["操作员"].ToString();
                }
                if (row["备注"] != null)
                {
                    model.备注 = row["备注"].ToString();
                }
                if (row["余额"] != null)
                {
                    model.余额 = row["余额"].ToString();
                }
                if (row["收支"] != null)
                {
                    model.收支 = row["收支"].ToString();
                }
            }
            return model;
        }

    }
}
